<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of account
 *
 * @author lz06
 */
class AccountDb {

    private $dbc;

    function __construct(DBC $dbc) {
        $this->dbc = $dbc;
    }

    /**
     * 根据用户名，密码获得用户信息
     * @param DBC $dbc
     * @param type $accountId
     * @param type $accountPass
     * @return type
     */
    public function getAccount($accountId, $accountPass) {
        $sql = "SELECT * FROM Account WHERE AccountID = '" . $accountId . "' AND accountpass = '" . $accountPass . "'";
        $data = $this->dbc->fetchData($sql);
        for ($i = 0; $i < count($data); $i++) {
            unset($data[$i]['accountpass']);
        }
        return $data;
    }

    /**
     * 用户更新密码
     * @param type $accUnique
     * @param type $accountId
     * @param type $oldPassword
     * @param type $newPassword
     * @return type
     */
    public function modifyPassword($accUnique, $accountId, $oldPassword, $newPassword) {
        $sql = "UPDATE Account SET accountpass = '" . $newPassword . "' WHERE AccUnique = '" . $accUnique . "' AND AccountID = '" . $accountId . "' AND accountpass = '" . $oldPassword . "'";
        return $this->dbc->execute($sql);
    }

    /**
     * 用户列表
     * @param type $keyword
     * @param type $start
     * @param type $limit
     * @return type
     */
    public function query($keyword, $start, $limit) {
        $sql = "SELECT TOP " . $limit . " * ,convert(varchar(21),recenttime,121) as recent,convert(varchar(21),outtime,121) as outTime2,"
                . "(select TOP 1 balance from AccountMoney where AccUnique = Account.AccUnique) AS balance FROM Account ";
        if ($keyword == "") {
            $sql .= " WHERE AccUnique NOT IN(SELECT TOP " . $start . " AccUnique FROM Account);";
        } else {
            $sql .= " WHERE AccountID LIEK '%" . $keyword . "%' AND AccUnique NOT IN(SELECT TOP " . $start . " AccUnique FROM Account);";
        }
        return $this->dbc->fetchData($sql);
    }

    public function getItemNumber($keyword) {
        if ($keyword == "") {
            $sql = "SELECT COUNT(AccUnique) AS totalNumber FROM Account;";
        } else {
            $sql = "SELECT COUNT(AccUnique) AS totalNumber FROM Account WHERE AccountID LIEK '%" . $keyword . "%';";
        }

        $data = $this->dbc->fetchData($sql);
        if (isArrayNotEmpty($data)) {
            return $data[0]['totalNumber'];
        }
        return 0;
    }

    public function getAccountById($accUnique) {
        $sql = "SELECT * FROM Account WHERE AccUnique = '" . $accUnique . "'";
        $data = $this->dbc->fetchData($sql);
        for ($i = 0; $i < count($data); $i++) {
            unset($data[$i]['accountpass']);
        }
        return $data;
    }

    /**
     * 根据名称获得用户
     * @param type $username
     * @return type
     */
    public function getAccountByName($username) {
        $sql = "SELECT * FROM Account WHERE AccountID = '" . $username . "'";
        $data = $this->dbc->fetchData($sql);
        for ($i = 0; $i < count($data); $i++) {
            unset($data[$i]['accountpass']);
        }
        return $data;
    }

    public function addNewAccount($username, $password) {
        $ip = $_SERVER['REMOTE_ADDR'];
        $sql = "INSERT INTO Account(AccountID,UserType,joinnum,recenttime,userip,accountpass,LoginTimeSum) " .
                "VALUES('" . $username . "','11','0',getDate(),'" . $ip . "','" . $password . "',0)";
        return $this->dbc->execute($sql);
    }
    
    public function getAccountByAccountId($accountId) {
        $sql = "SELECT * FROM Account WHERE AccountID = '" . $accountId . "'";
        return $this->dbc->fetchData($sql);
    }

}
